﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using Microsoft.Win32;
using MySql.Data.MySqlClient;
using ClosedXML.Excel;
using System.Data.SQLite;

namespace DBTools;

public partial class MainWindow : Window
{
	private readonly ObservableCollection<string> _databases = new();
	private readonly ObservableCollection<string> _tables = new();
	private readonly ObservableCollection<ColumnItem> _columns = new();

	private string? _currentProvider; // "SQLite" | "MySQL"
	private string? _selectedDatabase;
	private string? _selectedTable;

    public MainWindow()
    {
        InitializeComponent();

		DatabaseList.ItemsSource = _databases;
		TableList.ItemsSource = _tables;
		ColumnList.ItemsSource = _columns;

		ProviderCombo.SelectionChanged += (_, __) => OnProviderChanged();
		OnProviderChanged();
	}

	private void OnProviderChanged()
	{
		_currentProvider = (ProviderCombo.SelectedItem as ComboBoxItem)?.Content?.ToString();

		// 调整 UI：SQLite 显示浏览按钮，MySQL 则隐藏浏览按钮
		if (string.Equals(_currentProvider, "SQLite", StringComparison.OrdinalIgnoreCase))
		{
			BrowseButton.Visibility = Visibility.Visible;
		}
		else
		{
			BrowseButton.Visibility = Visibility.Collapsed;
		}
	}

	private void OnBrowseClick(object sender, RoutedEventArgs e)
	{
		try
		{
			var dlg = new OpenFileDialog
			{
				Filter = "SQLite 数据库|*.db;*.sqlite;*.sqlite3|所有文件|*.*"
			};
			if (dlg.ShowDialog(this) == true)
			{
				ConnectionText.Text = dlg.FileName;
			}
		}
		catch (Exception ex)
		{
			MessageBox.Show(this, ex.Message, "选择文件失败", MessageBoxButton.OK, MessageBoxImage.Error);
		}
	}

	private async void OnConnectClick(object sender, RoutedEventArgs e)
	{
		try
		{
			if (string.Equals(_currentProvider, "SQLite", StringComparison.OrdinalIgnoreCase))
			{
				await LoadSqliteAsync(ConnectionText.Text);
			}
			else if (string.Equals(_currentProvider, "MySQL", StringComparison.OrdinalIgnoreCase))
			{
				await LoadMySqlAsync(BuildMySqlConnectionString());
			}
			else
			{
				MessageBox.Show(this, "请选择数据库类型", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
			}
		}
		catch (Exception ex)
		{
			MessageBox.Show(this, ex.Message, "连接失败", MessageBoxButton.OK, MessageBoxImage.Error);
		}
	}

	private void OnDatabaseSelectionChanged(object sender, SelectionChangedEventArgs e)
	{
		_selectedDatabase = DatabaseList.SelectedItem as string;
		_tables.Clear();
		_columns.Clear();

		if (string.Equals(_currentProvider, "MySQL", StringComparison.OrdinalIgnoreCase) && !string.IsNullOrWhiteSpace(_selectedDatabase))
		{
			try
			{
				LoadMySqlTables(BuildMySqlConnectionString(database: _selectedDatabase));
			}
			catch (Exception ex)
			{
				MessageBox.Show(this, ex.Message, "加载表失败", MessageBoxButton.OK, MessageBoxImage.Error);
			}
		}
		else if (string.Equals(_currentProvider, "SQLite", StringComparison.OrdinalIgnoreCase))
		{
			// SQLite: 数据库固定为 main
			try
			{
				LoadSqliteTables(ConnectionText.Text);
			}
			catch (Exception ex)
			{
				MessageBox.Show(this, ex.Message, "加载表失败", MessageBoxButton.OK, MessageBoxImage.Error);
			}
		}
	}

	private void OnTableSelectionChanged(object sender, SelectionChangedEventArgs e)
	{
		_selectedTable = TableList.SelectedItem as string;
		_columns.Clear();

		if (string.IsNullOrWhiteSpace(_selectedTable)) return;

		try
		{
			if (string.Equals(_currentProvider, "SQLite", StringComparison.OrdinalIgnoreCase))
			{
				LoadSqliteColumns(ConnectionText.Text, _selectedTable);
			}
			else if (string.Equals(_currentProvider, "MySQL", StringComparison.OrdinalIgnoreCase))
			{
				if (string.IsNullOrWhiteSpace(_selectedDatabase)) return;
				LoadMySqlColumns(BuildMySqlConnectionString(database: _selectedDatabase), _selectedDatabase!, _selectedTable);
			}
		}
		catch (Exception ex)
		{
			MessageBox.Show(this, ex.Message, "加载字段失败", MessageBoxButton.OK, MessageBoxImage.Error);
		}
	}

	private void OnSelectAllColumns(object sender, RoutedEventArgs e)
	{
		foreach (var c in _columns) c.IsSelected = true;
		ColumnList.Items.Refresh();
	}

	private void OnDeselectAllColumns(object sender, RoutedEventArgs e)
	{
		foreach (var c in _columns) c.IsSelected = false;
		ColumnList.Items.Refresh();
	}

	private void OnExportClick(object sender, RoutedEventArgs e)
	{
		try
		{
			if (string.IsNullOrWhiteSpace(_selectedTable))
			{
				MessageBox.Show(this, "请先选择表", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
				return;
			}

			var selected = _columns.Where(c => c.IsSelected).ToList();
			if (selected.Count == 0)
			{
				MessageBox.Show(this, "请至少选择一个字段", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
				return;
			}

			var isExcel = (FormatCombo.SelectedItem as ComboBoxItem)?.Content?.ToString()?.Equals("Excel", StringComparison.OrdinalIgnoreCase) == true;

			var dlg = new SaveFileDialog
			{
				FileName = $"{_selectedTable}_columns" + (isExcel ? ".xlsx" : ".csv"),
				Filter = isExcel ? "Excel 文件 (*.xlsx)|*.xlsx" : "CSV 文件 (*.csv)|*.csv"
			};
			if (dlg.ShowDialog(this) != true) return;

			if (isExcel)
			{
				ExportColumnsToExcel(dlg.FileName, selected);
			}
			else
			{
				ExportColumnsToCsv(dlg.FileName, selected);
			}

			MessageBox.Show(this, "导出完成", "成功", MessageBoxButton.OK, MessageBoxImage.Information);
		}
		catch (Exception ex)
		{
			MessageBox.Show(this, ex.Message, "导出失败", MessageBoxButton.OK, MessageBoxImage.Error);
		}
	}

	// ===================== SQLite =====================
	private async System.Threading.Tasks.Task LoadSqliteAsync(string? path)
	{
		if (string.IsNullOrWhiteSpace(path) || !File.Exists(path))
			throw new InvalidOperationException("请选择有效的 SQLite 数据库文件");

		_databases.Clear();
		_tables.Clear();
		_columns.Clear();

		// SQLite 只有一个逻辑数据库
		_databases.Add("main");
		DatabaseList.SelectedIndex = 0;

		await System.Threading.Tasks.Task.Run(() => { /* 占位，保持一致的异步接口 */ });

		// 预加载表
		LoadSqliteTables(path);
	}

	private void LoadSqliteTables(string path)
	{
		using var conn = new SQLiteConnection($"Data Source={path};Version=3;");
		conn.Open();
		using var cmd = new SQLiteCommand("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;", conn);
		using var reader = cmd.ExecuteReader();

		_tables.Clear();
		while (reader.Read())
		{
			_tables.Add(reader.GetString(0));
		}
		if (_tables.Count > 0) TableList.SelectedIndex = 0;
	}

	private void LoadSqliteColumns(string path, string table)
	{
		using var conn = new SQLiteConnection($"Data Source={path};Version=3;");
		conn.Open();
		using var cmd = new SQLiteCommand($"PRAGMA table_info([{table}]);", conn);
		using var reader = cmd.ExecuteReader();

		_columns.Clear();
		while (reader.Read())
		{
			var name = reader[1]?.ToString() ?? string.Empty; // name
			var type = reader[2]?.ToString() ?? string.Empty; // type
			_columns.Add(new ColumnItem { Name = name, Type = type, IsSelected = true });
		}
	}

	// ===================== MySQL =====================
	private string BuildMySqlConnectionString(string? database = null)
	{
		// 若用户输入完整连接串，直接使用；否则从字段构造
		var text = ConnectionText.Text?.Trim();
		if (!string.IsNullOrWhiteSpace(text) && text.Contains('=') && text.Contains(';'))
		{
			var builder = new MySqlConnectionStringBuilder(text);
			if (!string.IsNullOrWhiteSpace(UserText.Text)) builder.UserID = UserText.Text.Trim();
			if (!string.IsNullOrWhiteSpace(PasswordBox.Password)) builder.Password = PasswordBox.Password;
			if (!string.IsNullOrWhiteSpace(database)) builder.Database = database;
			builder.AllowUserVariables = true;
			builder.Pooling = true;
			return builder.ConnectionString;
		}
		else
		{
			var builder = new MySqlConnectionStringBuilder
			{
				Server = text ?? "localhost",
				UserID = string.IsNullOrWhiteSpace(UserText.Text) ? "root" : UserText.Text.Trim(),
				Password = PasswordBox.Password ?? string.Empty,
				Database = database ?? string.Empty,
				AllowUserVariables = true,
				Pooling = true
			};
			return builder.ConnectionString;
		}
	}

	private async System.Threading.Tasks.Task LoadMySqlAsync(string connString)
	{
		_databases.Clear();
		_tables.Clear();
		_columns.Clear();

		await System.Threading.Tasks.Task.Run(() =>
		{
			using var conn = new MySqlConnection(connString);
			conn.Open();
			using var cmd = new MySqlCommand("SHOW DATABASES;", conn);
			using var reader = cmd.ExecuteReader();
			var list = new System.Collections.Generic.List<string>();
			while (reader.Read())
			{
				var db = reader.GetString(0);
				// 跳过系统库
				if (db.Equals("information_schema", StringComparison.OrdinalIgnoreCase) ||
					db.Equals("mysql", StringComparison.OrdinalIgnoreCase) ||
					db.Equals("performance_schema", StringComparison.OrdinalIgnoreCase) ||
					db.Equals("sys", StringComparison.OrdinalIgnoreCase))
					continue;
				list.Add(db);
			}

			Dispatcher.Invoke(() =>
			{
				foreach (var db in list) _databases.Add(db);
				if (_databases.Count > 0) DatabaseList.SelectedIndex = 0;
			});
		});
	}

	private void LoadMySqlTables(string connString)
	{
		using var conn = new MySqlConnection(connString);
		conn.Open();
		using var cmd = new MySqlCommand("SHOW TABLES;", conn);
		using var reader = cmd.ExecuteReader();

		_tables.Clear();
		while (reader.Read())
		{
			_tables.Add(reader.GetString(0));
		}
		if (_tables.Count > 0) TableList.SelectedIndex = 0;
	}

	private void LoadMySqlColumns(string connString, string database, string table)
	{
		using var conn = new MySqlConnection(connString);
		conn.Open();
		var sql = @"SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION;";
		using var cmd = new MySqlCommand(sql, conn);
		cmd.Parameters.AddWithValue("@db", database);
		cmd.Parameters.AddWithValue("@table", table);
		using var reader = cmd.ExecuteReader();

		_columns.Clear();
		while (reader.Read())
		{
			var name = reader.GetString(0);
			var type = reader.GetString(1);
			_columns.Add(new ColumnItem { Name = name, Type = type, IsSelected = true });
		}
	}

	// ===================== 导出 =====================
	private static void ExportColumnsToCsv(string filePath, System.Collections.Generic.IReadOnlyList<ColumnItem> columns)
	{
		var sb = new StringBuilder();
		// 以两列形式导出：Name,Type
		sb.AppendLine("Name,Type");
		foreach (var c in columns)
		{
			var name = EscapeCsv(c.Name);
			var type = EscapeCsv(c.Type);
			sb.Append(name).Append(',').Append(type).AppendLine();
		}
		var bytes = new UTF8Encoding(true).GetBytes(sb.ToString()); // UTF-8 BOM
		File.WriteAllBytes(filePath, bytes);
	}

	private static string EscapeCsv(string value)
	{
		if (value == null) return string.Empty;
		var needsQuote = value.Contains(',') || value.Contains('"') || value.Contains('\n') || value.Contains('\r');
		var v = value.Replace("\"", "\"\"");
		return needsQuote ? $"\"{v}\"" : v;
	}

	private static void ExportColumnsToExcel(string filePath, System.Collections.Generic.IReadOnlyList<ColumnItem> columns)
	{
		using var wb = new XLWorkbook();
		var ws = wb.AddWorksheet("Columns");
		ws.Cell(1, 1).SetValue("Name");
		ws.Cell(1, 2).SetValue("Type");
		for (int i = 0; i < columns.Count; i++)
		{
			ws.Cell(i + 2, 1).SetValue(columns[i].Name);
			ws.Cell(i + 2, 2).SetValue(columns[i].Type);
		}
		ws.Columns().AdjustToContents();
		wb.SaveAs(filePath);
	}

	// ===================== 数据导出（表数据） =====================
	private async void OnExportDataClick(object sender, RoutedEventArgs e)
	{
		try
		{
			if (string.IsNullOrWhiteSpace(_selectedTable))
			{
				MessageBox.Show(this, "请先选择表", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
				return;
			}
			var selectedCols = _columns.Where(c => c.IsSelected).Select(c => c.Name).ToList();
			if (selectedCols.Count == 0)
			{
				MessageBox.Show(this, "请至少选择一个字段", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
				return;
			}

			var where = WhereText?.Text?.Trim();
			var orderBy = OrderByText?.Text?.Trim();
			int? limit = null;
			if (int.TryParse(LimitText?.Text, out var l) && l > 0) limit = l;
			var includeHeader = IncludeHeaderCheck?.IsChecked == true;
			var isExcel = (FormatCombo.SelectedItem as ComboBoxItem)?.Content?.ToString()?.Equals("Excel", StringComparison.OrdinalIgnoreCase) == true;

			var dlg = new SaveFileDialog
			{
				FileName = $"{_selectedTable}_data" + (isExcel ? ".xlsx" : ".csv"),
				Filter = isExcel ? "Excel 文件 (*.xlsx)|*.xlsx" : "CSV 文件 (*.csv)|*.csv"
			};
			if (dlg.ShowDialog(this) != true) return;

			if (string.Equals(_currentProvider, "SQLite", StringComparison.OrdinalIgnoreCase))
			{
				await ExportSqliteDataAsync(ConnectionText.Text, _selectedTable!, selectedCols, where, orderBy, limit, dlg.FileName, includeHeader, isExcel);
			}
			else if (string.Equals(_currentProvider, "MySQL", StringComparison.OrdinalIgnoreCase))
			{
				if (string.IsNullOrWhiteSpace(_selectedDatabase))
				{
					MessageBox.Show(this, "请先选择数据库", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
					return;
				}
				await ExportMySqlDataAsync(BuildMySqlConnectionString(database: _selectedDatabase), _selectedTable!, selectedCols, where, orderBy, limit, dlg.FileName, includeHeader, isExcel);
			}
			else
			{
				MessageBox.Show(this, "请选择数据库类型", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
				return;
			}

			MessageBox.Show(this, "导出完成", "成功", MessageBoxButton.OK, MessageBoxImage.Information);
		}
		catch (Exception ex)
		{
			MessageBox.Show(this, ex.Message, "导出失败", MessageBoxButton.OK, MessageBoxImage.Error);
		}
	}

	private static string QuoteIdentSqlite(string name) => $"\"{name.Replace("\"", "\"\"")}\"";
	private static string QuoteIdentMySql(string name) => $"`{name.Replace("`", "``")}`";

	private static string BuildSelectSqlForSqlite(string table, System.Collections.Generic.IReadOnlyList<string> columns, string? where, string? orderBy, int? limit)
	{
		var colList = string.Join(", ", columns.Select(QuoteIdentSqlite));
		var sb = new StringBuilder();
		sb.Append("SELECT ").Append(colList).Append(" FROM ").Append(QuoteIdentSqlite(table));
		if (!string.IsNullOrWhiteSpace(where)) sb.Append(" WHERE ").Append(where);
		if (!string.IsNullOrWhiteSpace(orderBy)) sb.Append(" ORDER BY ").Append(orderBy);
		if (limit.HasValue) sb.Append(" LIMIT ").Append(limit.Value);
		return sb.ToString();
	}

	private static string BuildSelectSqlForMySql(string table, System.Collections.Generic.IReadOnlyList<string> columns, string? where, string? orderBy, int? limit)
	{
		var colList = string.Join(", ", columns.Select(QuoteIdentMySql));
		var sb = new StringBuilder();
		sb.Append("SELECT ").Append(colList).Append(" FROM ").Append(QuoteIdentMySql(table));
		if (!string.IsNullOrWhiteSpace(where)) sb.Append(" WHERE ").Append(where);
		if (!string.IsNullOrWhiteSpace(orderBy)) sb.Append(" ORDER BY ").Append(orderBy);
		if (limit.HasValue) sb.Append(" LIMIT ").Append(limit.Value);
		return sb.ToString();
	}

	private static async System.Threading.Tasks.Task ExportSqliteDataAsync(
		string? path,
		string table,
		System.Collections.Generic.IReadOnlyList<string> columns,
		string? where,
		string? orderBy,
		int? limit,
		string filePath,
		bool includeHeader,
		bool isExcel)
	{
		if (string.IsNullOrWhiteSpace(path) || !File.Exists(path))
			throw new InvalidOperationException("请选择有效的 SQLite 数据库文件");

		using var conn = new SQLiteConnection($"Data Source={path};Version=3;");
		await conn.OpenAsync();

		var sql = BuildSelectSqlForSqlite(table, columns, where, orderBy, limit);
		using var cmd = new SQLiteCommand(sql, conn);
		if (isExcel)
		{
			ExportReaderToExcel(cmd, filePath, includeHeader);
		}
		else
		{
			ExportReaderToCsv(cmd, filePath, includeHeader);
		}
	}

	private static async System.Threading.Tasks.Task ExportMySqlDataAsync(
		string connString,
		string table,
		System.Collections.Generic.IReadOnlyList<string> columns,
		string? where,
		string? orderBy,
		int? limit,
		string filePath,
		bool includeHeader,
		bool isExcel)
	{
		using var conn = new MySqlConnection(connString);
		await conn.OpenAsync();

		var sql = BuildSelectSqlForMySql(table, columns, where, orderBy, limit);
		using var cmd = new MySqlCommand(sql, conn);
		if (isExcel)
		{
			ExportReaderToExcel(cmd, filePath, includeHeader);
		}
		else
		{
			ExportReaderToCsv(cmd, filePath, includeHeader);
		}
	}

	private static void ExportReaderToCsv(IDbCommand cmd, string filePath, bool includeHeader)
	{
		using var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
		using var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.Read);
		var preamble = new UTF8Encoding(true).GetPreamble();
		fs.Write(preamble, 0, preamble.Length);
		using var sw = new StreamWriter(fs, new UTF8Encoding(true));

		if (includeHeader)
		{
			var header = new string[reader.FieldCount];
			for (int i = 0; i < reader.FieldCount; i++) header[i] = EscapeCsv(reader.GetName(i));
			sw.WriteLine(string.Join(",", header));
		}

		var values = new object[reader.FieldCount];
		while (reader.Read())
		{
			reader.GetValues(values);
			for (int i = 0; i < values.Length; i++)
			{
				values[i] = EscapeCsv(FormatValue(values[i]));
			}
			sw.WriteLine(string.Join(",", values));
		}
	}

	private static void ExportReaderToExcel(IDbCommand cmd, string filePath, bool includeHeader)
	{
		using var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
		using var wb = new XLWorkbook();
		var ws = wb.AddWorksheet("Data");

		int row = 1;
		int colCount = reader.FieldCount;

		if (includeHeader)
		{
			for (int c = 0; c < colCount; c++)
				ws.Cell(row, c + 1).SetValue(reader.GetName(c));
			row++;
		}

		var values = new object[colCount];
		while (reader.Read())
		{
			reader.GetValues(values);
			for (int c = 0; c < colCount; c++)
				ws.Cell(row, c + 1).SetValue(FormatValue(values[c]));
			row++;
		}

		ws.Columns().AdjustToContents();
		wb.SaveAs(filePath);
	}

	private static string FormatValue(object? v)
	{
		if (v == null || v is DBNull) return string.Empty;
		if (v is DateTime dt) return dt.ToString("yyyy-MM-dd HH:mm:ss");
		if (v is bool b) return b ? "true" : "false";
		return v.ToString() ?? string.Empty;
	}

	private static object FormatCellValue(object? v)
	{
		if (v == null || v is DBNull) return string.Empty;
		if (v is DateTime dt) return dt;
		if (v is bool b) return b;
		return v;
	}
}

public class ColumnItem
{
	public string Name { get; set; } = string.Empty;
	public string Type { get; set; } = string.Empty;
	public bool IsSelected { get; set; }
}